--Function that generates new column order index
create or replace function p_generate_order_index(
    --Table id
    a_table_id raw)
    return number
as
    a_order_index number(5);
begin
    select max(order_index) + 1 into a_order_index
    from meta_table_columns
    where table_id = a_table_id;
    
     if a_order_index is null then
        a_order_index := 0;
    end if;
    
    return a_order_index;
end;

--go

--Function of checking that column with the specified id and name doesn't exist
create or replace procedure p_check_col_not_exists(
    --Table id
    a_table_id raw,
    --Column id
    a_column_id raw,
    --Column name
    a_column_name varchar2)
as
    --Row count
    a_row_count number;
begin
    --Searching for the column id
    select count(*)
    into a_row_count
    from meta_table_columns
    where column_id = a_column_id;
    
    if a_row_count > 0 then
        raise_application_error(-20020,
                'A column with the specified id was already added (column_id = '
                || a_column_id
                || ') (#error_code = 20020)');
    end if;
    
    --Searching for the column name
    select count(*)
    into a_row_count
    from meta_table_columns
    where table_id = a_table_id and column_name = a_column_name;
    
    if a_row_count > 0 then
        raise_application_error(-20021,
                'A column with the specified name was already added (column_name = '
                || a_column_name
                || ') (#error_code = 20021)');
    end if;
end;

--go

--Function of adding string column
create or replace procedure meta_add_string_column(
    --Table id
    a_table_id raw,
    --Column id
    a_column_id raw,
    --Column name
    a_column_name varchar2)
as
    --Table name
    a_table_name varchar2(26);
begin
    p_check_col_not_exists(a_table_id, a_column_id, a_column_name);
    
    a_table_name :=  p_get_table_name(a_table_id);
    
    --Changing the metadata
    insert into meta_string_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values(a_column_id, a_table_id, a_column_name,
        p_generate_order_index(a_table_id), 1);

    --Dropping table view
    p_drop_view_if_exists(a_table_name);

    execute immediate 'alter table tbl_' 
    || a_table_name
    || ' add ' || a_column_name
    || ' nvarchar2(2000)';
        
    --Creating table view
    p_create_table_view(a_table_id);    
end;

--go

--Function of checking column existence
create or replace procedure p_check_col_exists(
    --Column id
    a_column_id raw)
as
    --Row count
    a_row_count number;
begin
    --Searching for the column
    select count(*)
    into a_row_count
    from meta_table_columns
    where column_id = a_column_id;
    
    if a_row_count = 0 then
        raise_application_error(-20019,
                'Specified column was not found (column_id = '
                || a_column_id
                || ') (#error_code = 20019)');
    end if;  
end;

--go

--Function of checking column type
create or replace procedure p_check_col_type(
    --Column id
    a_column_id raw,
    --Column table name
    a_column_table_name varchar2,
    --Column type name
    a_column_type_name varchar2)
as
    --Row count
    a_row_count number;
begin
    execute immediate 'select count(*) from '
    || a_column_table_name
    || ' where column_id = :1'
    into a_row_count
    using a_column_id;
    
    if a_row_count = 0 then
        raise_application_error(-20019,
                'Specified column (of type '
                || a_column_type_name
                || ') was not found (column_id = '
                || a_column_id
                || ') (#error_code = 20019)');
    end if;
end;

--go

--Function of removing string column
create or replace procedure meta_delete_column(
    --Column id
    a_column_id raw)
as
    --Table id
    a_table_id raw(16);
    --Column name
    a_column_name varchar(26);
    --Table name
    a_table_name varchar2(26);
    --Table column name
    a_column_table_name varchar2(30);
    --Customization level
    a_customization_level number(10);
begin
    p_check_col_exists(a_column_id);

    --Reading column properties
    select table_id, column_name, column_type, customization_level
    into a_table_id, a_column_name, a_column_table_name, a_customization_level
    from meta_table_columns
    where column_id = a_column_id;
    
    --If this is ref column
    if a_column_table_name like '%meta_ref_columns' then
        --Dropping ref checking trigger
        p_drop_ref_checking(a_column_id);
    end if;
    
    --If this is a system column
    if a_customization_level = 0 then
        raise_application_error(-20005,
                'Deleting system column is forbidden (column_id = '
                || a_column_id
                || ') (#error_code = 20005)');
    end if;   

    a_table_name := p_get_table_name(a_table_id);
    
    --Deleting column indexes (if any)
    p_delete_column_indexes(a_column_id);
    
    --Dropping table view
    p_drop_view_if_exists(a_table_name);
    
     execute immediate 'alter table tbl_'
    || a_table_name
    || ' drop column ' || a_column_name
    || ' cascade constraints';
    
    --Updating the metadata
    execute immediate 'delete from ' || a_column_table_name
    || ' where column_id = :1'
    using a_column_id;
    
    --Creating table view
    p_create_table_view(a_table_id);
end;

--go

--Function of renaming string column
create or replace procedure meta_rename_column(
    --Column id
    a_column_id raw,
    --New column name
    a_new_column_name varchar2)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Old column name
    a_old_column_name varchar2(26);
    --Column renaming feature
    column_renaming_is_enabled number(1) := 0;
    --Column table name
    a_column_table_name varchar2(30);
    --Customization level
    a_customization_level number(10);
    --Row count
    a_row_count number;
begin
    p_check_col_exists(a_column_id);
    
    --Reading column properties
    select table_id, column_name, column_type, customization_level
    into a_table_id, a_old_column_name, a_column_table_name, a_customization_level
    from meta_table_columns
    where column_id = a_column_id;
    
    --Searching for the column with duplicate name
    select count(*) into a_row_count
    from meta_table_columns
    where table_id = a_table_id and column_name = a_new_column_name;
    
    if a_row_count > 0 then
        raise_application_error(-20021,
                'A column with the specified name was already added (column_name = '
                || a_new_column_name
                || ') (#error_code = 20021)');
    end if;

    --If there is no need fo renaming
    if a_old_column_name = a_new_column_name then
        return;
    end if;
    
    --If this is a system column
    if a_customization_level = 0 then
        raise_application_error(-20006,
                'Renaming system column is forbidden (column_id = '
                || a_column_id
                || ') (#error_code = 20006)');
    end if; 
    
    a_table_name :=  p_get_table_name(a_table_id);
    
    --Checking column renaming
    select is_enabled into column_renaming_is_enabled
    from meta_dbms_features
    where feature_name = 'COLUMN_RENAMING';
    
    --If feature is enabled
    if column_renaming_is_enabled = 1 then
        
        --Changing the metadata
        execute immediate 'update ' || a_column_table_name
        || ' set column_name = :1 where column_id = :2'
        using a_new_column_name, a_column_id;
        
        --Dropping table view
        p_drop_view_if_exists(a_table_name);
        
        --If this is ref column
        if a_column_table_name like '%meta_ref_columns' then
            --Dropping ref checking trigger
            p_drop_ref_checking(a_column_id);
        end if;
        
        execute immediate 'alter table tbl_'
        || a_table_name
        || ' rename column ' || a_old_column_name 
        || ' to ' || a_new_column_name;
        
        --If this is ref column
        if a_column_table_name like '%meta_ref_columns' then
            --Creating ref checking trigger
            p_create_ref_checking(a_column_id);
        end if;
        
        --Recreating column index triggers
        p_recreate_col_index_triggers(a_column_id);
        
        --Creating table view
        p_create_table_view(a_table_id);        
    else
        raise_application_error(-20007,
                'Renaming a column is not supported by the dbms (column_id = '
                || a_column_id
                || ') (#error_code = 20007)');
    end if;
end;

--go

--Function of setting column nullability
create or replace procedure meta_set_column_nullability(
    --Column id
    a_column_id raw,
    --Is not null
    a_is_not_null integer)
as
    --Table id
    a_table_id raw(16);
    --Backend table name
    a_column_table_name varchar2(30);
    --Column name
    a_column_name varchar2(26);
    --Old is_not_null
    a_old_is_not_null integer;
    --Table name
    a_table_name varchar2(26);
    --Customization level
    a_customization_level number(10);
begin
    p_check_col_exists(a_column_id);
    
    --Reading column table name
    select column_type, customization_level
    into a_column_table_name, a_customization_level
    from meta_table_columns
    where column_id = a_column_id;
    
    --If this is a system column
    if a_customization_level = 0 then
        raise_application_error(-20008,
                'Changing system column nullability is forbidden (column_id = '
                || a_column_id
                || ') (#error_code = 20008)');
    end if;
    
    --Reading column properties
    execute immediate 'select table_id, is_not_null, column_name from '
    ||  a_column_table_name || ' where column_id = :1'
    into a_table_id, a_old_is_not_null, a_column_name
    using a_column_id;
    
    a_table_name := p_get_table_name(a_table_id);
    
    if a_is_not_null = a_old_is_not_null then
        return;
    end if;
    
    if a_is_not_null = 1 then
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' not null)';
    else
         execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' null)';
    end if;
    
    --Dropping table view (otherwise, the view can be unvalid)
    p_drop_view_if_exists(a_table_name);
    
    --Creating table view
    p_create_table_view(a_table_id);
    
    --Changing the metadata
    execute immediate 'update ' || a_column_table_name
    || ' set is_not_null = :1 where column_id = :2'
    using a_is_not_null, a_column_id;
end;

--go

--Function of setting column default value
create or replace procedure meta_set_string_default(
    --Column id
    a_column_id raw,
    --Default value
    a_default_value nvarchar2)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Customization level
    a_customization_level number(10);
    --SQL cursor
    c integer;
    r integer;
    str nvarchar2(32767);
begin
    p_check_col_type(a_column_id, 'meta_string_columns', 'string');
    
    --Reading column properties
    select table_id, column_name, customization_level
    into a_table_id, a_column_name, a_customization_level
    from meta_string_columns
    where column_id = a_column_id;
    
    --If this is a system column
    if a_customization_level = 0 then
         raise_application_error(-20008,
                'Changing string system column default value is forbidden (column_id = '
                || a_column_id
                || ') (#error_code = 20008)');
    end if;
    
    a_table_name := p_get_table_name(a_table_id);
     
     if a_default_value is not null then
        str := 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' default ''' || a_default_value || ''')';
    else
        str := 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' default null)'; 
    end if;
    
    --execute immediate is not used because it doesn't support nvarchar2
    c := dbms_sql.open_cursor;
    dbms_sql.parse (c, str, dbms_sql.native);
    r := dbms_sql.execute (c);
    dbms_sql.close_cursor (c);
    
    --Updating the metadata
    update meta_string_columns
    set default_value = a_default_value
    where column_id = a_column_id;
end;

--go

--Function of setting string column max length
create or replace procedure meta_set_string_max_length(
    --Column id
    a_column_id raw,
    --Max length
    a_max_length integer)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Customization level
    a_customization_level number(10);
begin
    p_check_col_type(a_column_id, 'meta_string_columns', 'string');
    
    --Reading column properties
    select table_id, column_name, customization_level
    into a_table_id, a_column_name, a_customization_level
    from meta_string_columns
    where column_id = a_column_id;
    
    --If this is a system column
    if a_customization_level = 0 then
        raise_application_error(-20008,
                'Changing string system column max length is forbidden (column_id = '
                || a_column_id
                || ') (#error_code = 20008)');
    end if;

    a_table_name := p_get_table_name(a_table_id);

    if a_max_length is not null then
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' nvarchar2(' || a_max_length || '))';
    else
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' nvarchar2(2000))';
    end if;
    
    --Updating the metadata
    update meta_string_columns
    set max_length = a_max_length
    where column_id = a_column_id;
end;

--go
